﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Documents;
using System.Windows.Forms;
using MiniExcelLibs;
using NUnit.Framework;
using static System.Net.Mime.MediaTypeNames;

namespace 校验文件
{
    public partial class Form1 : Form
    {
        [Obsolete]
        public Form1()
        {
            InitializeComponent();
        }

        [Obsolete]
        private void button1_Click(object sender, EventArgs e)
        {
            Boolean result = true;
            #region 计量单位组
            DataTable table_JLDWZSR = MiniExcel.QueryAsDataTable("C:\\输入\\计量单位组.xlsx", useHeaderRow: true);
            DataTable table_JLDWZSC = new DataTable();
            DataColumn col1 = new DataColumn();
            col1.ColumnName = "组编码";
            table_JLDWZSC.Columns.Add(col1);
            DataColumn col2 = new DataColumn();
            col2.ColumnName = "组名称";
            table_JLDWZSC.Columns.Add(col2);
            DataColumn col3 = new DataColumn();
            col3.ColumnName = "换算类型";
            table_JLDWZSC.Columns.Add(col3);
            DataColumn col4 = new DataColumn();
            col4.ColumnName = "结果";
            table_JLDWZSC.Columns.Add(col4);

            //List<string> ZuBianMa = new List<string>();
            //List<string> ZuMingCheng = new List<string>();
            //List<string> HuanSuanLeiXing = new List<string>();
            foreach (DataRow row in table_JLDWZSR.Rows)
            {
                string ZBM = row[0].ToString();
                string ZMC = row[1].ToString();
                string HSLX = row[2].ToString();
                DataRow row2 = table_JLDWZSC.NewRow();
                row2[0] = ZBM;
                row2[1] = ZMC;
                row2[2] = HSLX;
                //demo1
                
                string query2 = "组编码='" + ZBM + "'";

                if (table_JLDWZSC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[3] = "组编码重复";

                }
                else
                {
                    //ZuBianMa.Add(ZBM);

                }
                query2 = "组名称='" + ZMC + "'";

                if (table_JLDWZSC.Select(query2).Count() > 0)
                //if (ZuMingCheng.Contains(ZMC))
                {
                    result = false;
                    row2[3] = "组名称重复";
                }
                else
                {
                    //ZuMingCheng .Add(ZMC);
                }
                if (HSLX == "1" || HSLX == "2" || HSLX == "0")
                {

                    //HuanSuanLeiXing.Add(HSLX);
                }
                else
                {
                    result = false;
                    row2[3] = "换算类型错误";
                }
                table_JLDWZSC.Rows.Add(row2);
            }
            MiniExcel.SaveAs("C:\\输出\\计量单位组校验结果.xlsx", table_JLDWZSC);
            #endregion
            if (result)
            {
                MessageBox.Show("计量单位组校验成功");
            }
            else
            {
                MessageBox.Show("计量单位组校验失败");
                return;
            }
            #region 计量单位
            DataTable table_JLDWSR = MiniExcel.QueryAsDataTable("C:\\输入\\计量单位.xlsx", useHeaderRow: true);
            DataTable table_JLDWSC = new DataTable();
            DataColumn col5 = new DataColumn();
            col5.ColumnName = "单位编码";
            table_JLDWSC.Columns.Add(col5);
            DataColumn col6 = new DataColumn();
            col6.ColumnName = "单位名称";
            table_JLDWSC.Columns.Add(col6);
            DataColumn col7 = new DataColumn();
            col7.ColumnName = "组编码";
            table_JLDWSC.Columns.Add(col7);
            DataColumn col8 = new DataColumn();
            col8.ColumnName = "主计量";
            table_JLDWSC.Columns.Add(col8);
            DataColumn col9 = new DataColumn();
            col9.ColumnName = "换算率";
            table_JLDWSC.Columns.Add(col9);
            DataColumn col10 = new DataColumn();
            col10.ColumnName = "结果";
            table_JLDWSC.Columns.Add(col10);
            IEnumerable<IGrouping<string, DataRow>> ZuBianMa = table_JLDWSR.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr["组编码"].ToString());
            foreach (IGrouping<string, DataRow> ig in ZuBianMa)
            {
                string ZBM = ig.Key.ToString();
                string query4 = "组编码='" + ZBM + "'";
                string HSLX = table_JLDWSR.Select(query4).FirstOrDefault()[2].ToString();
                //单独处理无换算率的
                if (HSLX == "0")
                {
                    DataRow[] rows = table_JLDWSR.Select(query4);
                    foreach (DataRow row in rows)
                    {
                        DataRow dataRow = table_JLDWSC.NewRow();
                        dataRow[0] = row[0];
                        dataRow[1] = row[1];
                        dataRow[2] = row[2];
                        dataRow[3] = row[3];
                        dataRow[4] = row[4];
                        query4 = "单位编码='" + row[0] + "'";
                        if (table_JLDWSC.Select(query4).Count() > 0)
                        {
                            dataRow[5] = "单位编码重复";
                            result = false;
                        }
                        table_JLDWSC.Rows.Add(dataRow);
                    }
                }
                //有换算率的
                else
                {
                    DataRow[] rows = table_JLDWSR.Select(query4);
                    //判断是否为两条
                    if (rows.Count() != 2)
                    {
                        foreach (DataRow row in rows)
                        {
                            DataRow dataRow = table_JLDWSC.NewRow();
                            dataRow[0] = row[0];
                            dataRow[1] = row[1];
                            dataRow[2] = row[2];
                            dataRow[3] = row[3];
                            dataRow[4] = row[4];
                            dataRow[5] = "该计量单位组未成对出现";
                            result = false;
                            query4 = "单位编码='" + row[0] + "'";
                            if (table_JLDWSC.Select(query4).Count() > 0)
                            {
                                dataRow[5] = dataRow[5] + ";" + "单位编码重复";
                                result = false;
                            }
                            table_JLDWSC.Rows.Add(dataRow);


                        }



                    }
                    //如果该计量单位组并非一主计量单位加辅计量单位
                    else if ((Convert.ToInt16(rows[0][3]) + Convert.ToInt16(rows[1][3])) != 1)
                    {
                        foreach (DataRow row in rows)
                        {
                            DataRow dataRow = table_JLDWSC.NewRow();
                            dataRow[0] = row[0];
                            dataRow[1] = row[1];
                            dataRow[2] = row[2];
                            dataRow[3] = row[3];
                            dataRow[4] = row[4];
                            dataRow[5] = dataRow[5] + ";" + "该计量单位组并非一主一辅";
                            result = false;
                            query4 = "单位编码='" + row[0] + "'";
                            if (table_JLDWSC.Select(query4).Count() > 0)
                            {
                                dataRow[5] = dataRow[5] + ";" + "单位编码重复";
                                result = false;
                            }

                            table_JLDWSC.Rows.Add(dataRow);

                        }
                    }
                    //判断两条对应的计量单位和辅计量单位是否相符
                    else
                    {

                        query4 = "组编码='" + ZBM + "' and 主计量='1' ";
                        if (table_JLDWSC.Select(query4).Count() == 1)
                        {
                            foreach (DataRow row in rows)
                            {
                                DataRow dataRow = table_JLDWSC.NewRow();
                                dataRow[0] = row[0];
                                dataRow[1] = row[1];
                                dataRow[2] = row[2];
                                dataRow[3] = row[3];
                                dataRow[4] = row[4];
                                result = false;
                                query4 = "单位编码='" + row[0] + "'";
                                if (table_JLDWSC.Select(query4).Count() > 0)
                                {
                                    dataRow[5] = dataRow[5] + ";" + "单位编码重复";
                                    result = false;
                                }
                                if (Convert.ToInt16(dataRow[4]) != 1 & Convert.ToInt16(dataRow[3]) == 1)
                                {
                                    dataRow[5] = dataRow[5] + ";" + "主计量单位换算率不为1";
                                    result = false;

                                }
                                if (Convert.ToInt16(dataRow[4]) <= 0 & Convert.ToInt16(dataRow[3]) == 0)
                                {
                                    dataRow[5] = dataRow[5] + ";" + "辅计量单位换算错误";
                                    result = false;

                                }

                                table_JLDWSC.Rows.Add(dataRow);

                            }
                        }
                        //如果没有名称行数等于一的  剩下的写入
                        else
                        {
                            foreach (DataRow row in rows)
                            {
                                DataRow dataRow = table_JLDWSC.NewRow();
                                dataRow[0] = row[0];
                                dataRow[1] = row[1];
                                dataRow[2] = row[2];
                                dataRow[3] = row[3];
                                dataRow[4] = row[4];
                                
                                query4 = "单位编码='" + row[0] + "'";


                                table_JLDWSC.Rows.Add(dataRow);

                            }
                        }



                    }

                }
            }
            MiniExcel.SaveAs("C:\\输出\\计量单位校验结果.xlsx", table_JLDWSC);
            #endregion
            if (result)
            {
                MessageBox.Show("计量单位校验成功");
            }
            else
            {
                MessageBox.Show("计量单位校验失败");
                return;
            }
            #region 存货分类

            DataTable table_CHFLSR = MiniExcel.QueryAsDataTable("C:\\输入\\存货分类.xlsx", useHeaderRow: true);
            DataTable table_CHFLSC = new DataTable();
            DataColumn col11 = new DataColumn();
            col11.ColumnName = "类别编码";
            table_CHFLSC.Columns.Add(col11);
            DataColumn col12 = new DataColumn();
            col12.ColumnName = "类别名称";
            table_CHFLSC.Columns.Add(col12);
            DataColumn col13 = new DataColumn();
            col13.ColumnName = "级次";
            table_CHFLSC.Columns.Add(col13);
            DataColumn col14 = new DataColumn();
            col14.ColumnName = "是否末级";
            table_CHFLSC.Columns.Add(col14);
            DataColumn col15 = new DataColumn();
            col15.ColumnName = "存货分类结果";
            table_CHFLSC.Columns.Add(col15);
            DataRow[] test = table_CHFLSR.Select("", "级次 desc");
            string query = string.Empty;

            for (int i = 1; i <= Convert.ToInt16(test[0][2]); i++)
            {
                query = "级次=" + i.ToString();
                DataRow[] CIJI = table_CHFLSR.Select(query);
                if (i == 1)
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_CHFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = "类别编码='" + row2[0] + "'";
                        string LBMC = string.Empty;
                        LBMC = "类别名称='" + row2[1] + "'";
                        //if不等于2
                        //输出编码错误
                        if (row1[0].ToString().Length != 2)
                        {
                            row2[4] = "类别编码长度错误";
                            result = false;
                        }
                        if (table_CHFLSC.Select(LBBM).Count() > 0)
                        {
                            row2[4] = "类别编码重复";
                            result = false;
                        }
                        if (table_CHFLSC.Select(LBMC).Count() > 1)
                        {
                            row2[4] = "类别名称重复";
                            result = false;
                        }
                        table_CHFLSC.Rows.Add(row2);
                    }

                }
                else
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_CHFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = row2[0].ToString();
                        string LBMC = string.Empty;
                        LBMC = row2[1].ToString();
                        //if大于2
                        //输出编码错误
                        string DQBM = string.Empty;
                        string FUBM = string.Empty;
                        string SFMJ = string.Empty;
                        string LBBM1 = string.Empty;
                        string LBBM2 = string.Empty;
                        DQBM = row2[0].ToString();
                        if (i == 2)
                        {
                            FUBM = DQBM.Substring(0, 2);
                            if (row1[0].ToString().Length != 4)
                            {
                                row2[4] = "类别编码长度错误";
                                result = false;
                            }
                        }
                        if (i == 3)
                        {
                            FUBM = DQBM.Substring(0, 4);
                            if (row1[0].ToString().Length != 7)
                            {
                                row2[4] = "类别编码长度错误";
                                result = false;
                            }
                        }

                        else
                        { }
                        if (table_CHFLSC.Select("类别编码 = '" + LBBM + "'").Count() > 1)
                        {
                            row2[4] = "类别编码重复";
                            result = false;
                        }
                        //
                        //table_CHFLSC.Rows.Add(row2);

                        //    else
                        //    if编码有重复的
                        //        输出编码重复

                        //if (table_CHFLSC.Select(LBMC).Count() > 1)
                        //{
                        //    row2[4] = "类别名称重复";
                        //    result = false;
                        //}
                        LBBM1 = "类别编码='" + FUBM + "'";
                        DataRow[] FYJBM = table_CHFLSR.Select(LBBM1);
                        if (FYJBM.Count() == 0)
                        {
                            row2[4] = "未找到上级编码";
                            result = false;
                        }
                        else
                        {
                            if (Convert.ToInt16(FYJBM[0][3]) == 1)
                            {
                                row2[4] = "上级编码为末级";
                                result = false;
                            }
                            LBBM2 = "类别编码 like '" + FUBM + "%' and 级次 = '" + (Convert.ToInt16(FYJBM[0][2]) + 1) + "' and 类别名称='" + LBMC + "'";
                            DataRow[] LBMC1 = table_CHFLSC.Select(LBBM2);
                            if (LBMC1.Count() > 0)
                            {
                                row2[4] = "类别名称重复";
                                result = false;
                            }
                        }



                        table_CHFLSC.Rows.Add(row2);
                    }
                }
                
            }
            MiniExcel.SaveAs("C:\\输出\\存货分类校验结果.xlsx", table_CHFLSC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("存货分类校验成功");
            }
            else
            {
                MessageBox.Show("存货分类校验失败");
                return;
            }
            #region 客户分类

            DataTable table_KHFLSR = MiniExcel.QueryAsDataTable("C:\\输入\\客户分类.xlsx", useHeaderRow: true);
            DataTable table_KHFLSC = table_KHFLSR.Clone();
            DataColumn col20 = new DataColumn();
            col20.ColumnName = "客户分类结果";
            table_KHFLSC.Columns.Add(col20);
            DataRow[] test1 = table_KHFLSR.Select("", "级次 desc");
            string query1 = string.Empty;

            for (int i = 1; i <= Convert.ToInt16(test[0][2]); i++)
            {
                query1 = "级次=" + i.ToString();
                DataRow[] CIJI = table_KHFLSR.Select(query1);
                if (i == 1)
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_KHFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = "类别编码='" + row2[0] + "'";
                        string LBMC = string.Empty;
                        LBMC = "类别名称='" + row2[1] + "'";
                        //if不等于2
                        //输出编码错误
                        if (row1[0].ToString().Length != 2)
                        {
                            row2[4] = "类别编码长度错误";
                            result = false;
                        }
                        if (table_KHFLSC.Select(LBBM).Count() > 0)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        if (table_KHFLSC.Select(LBMC).Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别名称重复";
                            result = false;
                        }
                        table_KHFLSC.Rows.Add(row2);
                    }

                }
                else
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_KHFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = row2[0].ToString();
                        string LBMC = string.Empty;
                        LBMC = row2[1].ToString();
                        //if大于2
                        //输出编码错误
                        string DQBM = string.Empty;
                        string FUBM = string.Empty;
                        string SFMJ = string.Empty;
                        string LBBM1 = string.Empty;
                        string LBBM2 = string.Empty;
                        DQBM = row2[0].ToString();
                        if (i == 2)
                        {
                            FUBM = DQBM.Substring(0, 2);
                            if (row1[0].ToString().Length != 4)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }

                        }
                        if (i == 3)
                        {
                            FUBM = DQBM.Substring(0, 4);
                            if (row1[0].ToString().Length != 7)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }
                          
                        }

                        else
                        { 
                        
                        }
                        if (table_KHFLSC.Select("类别编码 = '" + LBBM + "'").Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        //
                        //table_CHFLSC.Rows.Add(row2);

                        //    else
                        //    if编码有重复的
                        //        输出编码重复

                        //if (table_CHFLSC.Select(LBMC).Count() > 1)
                        //{
                        //    row2[4] = "类别名称重复";
                        //    result = false;
                        //}
                        LBBM1 = "类别编码='" + FUBM + "'";
                        DataRow[] FYJBM = table_KHFLSR.Select(LBBM1);
                        if (FYJBM.Count() == 0)
                        {
                            row2[4] = row2[4] + ";" + "未找到上级编码";
                            result = false;
                        }
                        else
                        {
                            if (Convert.ToInt16(FYJBM[0][3]) == 1)
                            {
                                row2[4] = row2[4] + ";" + "上级编码为末级";
                                result = false;
                            }
                            LBBM2 = "类别编码 like '" + FUBM + "%' and 级次 = '" + (Convert.ToInt16(FYJBM[0][2]) + 1) + "' and 类别名称='" + LBMC + "'";
                            DataRow[] LBMC1 = table_KHFLSC.Select(LBBM2);
                            if (LBMC1.Count() > 0)
                            {
                                row2[4] = row2[4] + ";" + "类别名称重复";
                                result = false;
                            }
                        }
                        table_KHFLSC.Rows.Add(row2);
                    }
                }

            }
            MiniExcel.SaveAs("C:\\输出\\客户分类校验结果.xlsx", table_KHFLSC );
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("客户分类校验成功");
            }
            else
            {
                MessageBox.Show("分类校验失败");
                return;
            }
            #region 供应商分类

            DataTable table_GYSFLSR = MiniExcel.QueryAsDataTable("C:\\输入\\供应商分类.xlsx", useHeaderRow: true);
            DataTable table_GYSFLSC = table_GYSFLSR.Clone();
            
            DataColumn col21 = new DataColumn();
            col21.ColumnName = "供应商分类结果";
            table_GYSFLSC.Columns.Add(col21);
            DataRow[] test2 = table_GYSFLSR.Select("", "级次 desc");
            string query3 = string.Empty;

            for (int i = 1; i <= Convert.ToInt16(test[0][2]); i++)
            {
                query3 = "级次=" + i.ToString();
                DataRow[] CIJI = table_GYSFLSR.Select(query3);
                if (i == 1)
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_GYSFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = "类别编码='" + row2[0] + "'";
                        string LBMC = string.Empty;
                        LBMC = "类别名称='" + row2[1] + "'";
                        //if不等于2
                        //输出编码错误
                        if (row1[0].ToString().Length != 2)
                        {
                            row2[4] = "类别编码长度错误";
                            result = false;
                        }
                        if (table_GYSFLSC.Select(LBBM).Count() > 0)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        if (table_GYSFLSC.Select(LBMC).Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别名称重复";
                            result = false;
                        }
                        table_GYSFLSC.Rows.Add(row2);
                    }

                }
                else
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_GYSFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = row2[0].ToString();
                        string LBMC = string.Empty;
                        LBMC = row2[1].ToString();
                        //if大于2
                        //输出编码错误
                        string DQBM = string.Empty;
                        string FUBM = string.Empty;
                        string SFMJ = string.Empty;
                        string LBBM1 = string.Empty;
                        string LBBM2 = string.Empty;
                        DQBM = row2[0].ToString();
                        if (i == 2)
                        {
                            FUBM = DQBM.Substring(0, 2);
                            if (row1[0].ToString().Length != 4)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }
                        }
                        if (i == 3)
                        {
                            FUBM = DQBM.Substring(0, 4);
                            if (row1[0].ToString().Length != 7)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }
                        }

                        else
                        { }
                        if (table_GYSFLSC.Select("类别编码 = '" + LBBM + "'").Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        //
                        //table_CHFLSC.Rows.Add(row2);

                        //    else
                        //    if编码有重复的
                        //        输出编码重复

                        //if (table_CHFLSC.Select(LBMC).Count() > 1)
                        //{
                        //    row2[4] = "类别名称重复";
                        //    result = false;
                        //}
                        LBBM1 = "类别编码='" + FUBM + "'";
                        DataRow[] FYJBM = table_GYSFLSR.Select(LBBM1);
                        if (FYJBM.Count() == 0)
                        {
                            row2[4] = row2[4] + ";" + "未找到上级编码";
                            result = false;
                        }
                        else
                        {
                            if (Convert.ToInt16(FYJBM[0][3]) == 1)
                            {
                                row2[4] = row2[4] + ";" + "上级编码为末级";
                                result = false;
                            }
                            LBBM2 = "类别编码 like '" + FUBM + "%' and 级次 = '" + (Convert.ToInt16(FYJBM[0][2]) + 1) + "' and 类别名称='" + LBMC + "'";
                            DataRow[] LBMC1 = table_GYSFLSC.Select(LBBM2);
                            if (LBMC1.Count() > 0)
                            {
                                row2[4] = row2[4] + ";" + "类别名称重复";
                                result = false;
                            }
                        }



                        table_GYSFLSC.Rows.Add(row2);
                    }
                }

            }
            MiniExcel.SaveAs("C:\\输出\\供应商分类校验结果.xlsx", table_GYSFLSC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("供应商分类校验成功");
            }
            else
            {
                MessageBox.Show("供应商分类校验失败");
                return;
            }
            #region 地区分类

            DataTable table_DQFLSR = MiniExcel.QueryAsDataTable("C:\\输入\\地区分类.xlsx", useHeaderRow: true);
            DataTable table_DQFLSC = table_DQFLSR.Clone();
            DataColumn col22 = new DataColumn();
            col22.ColumnName = "地区分类结果";
            table_DQFLSC.Columns.Add(col22);
            DataRow[] test3 = table_DQFLSR.Select("", "级次 desc");
            string query5 = string.Empty;

            for (int i = 1; i <= Convert.ToInt16(test[0][2]); i++)
            {
                query5 = "级次=" + i.ToString();
                DataRow[] CIJI = table_DQFLSR.Select(query5);
                if (i == 1)
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_DQFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = "类别编码='" + row2[0] + "'";
                        string LBMC = string.Empty;
                        LBMC = "类别名称='" + row2[1] + "'";
                        //if不等于2
                        //输出编码错误
                        if (row1[0].ToString().Length != 2)
                        {
                            row2[4] = "类别编码长度错误";
                            result = false;
                        }
                        if (table_DQFLSC.Select(LBBM).Count() > 0)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        if (table_DQFLSC.Select(LBMC).Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别名称重复";
                            result = false;
                        }
                        table_DQFLSC.Rows.Add(row2);
                    }

                }
                else
                {
                    foreach (DataRow row1 in CIJI)
                    {
                        //取出第0列长度
                        DataRow row2 = table_DQFLSC.NewRow();
                        row2[0] = row1[0];
                        row2[1] = row1[1];
                        row2[2] = row1[2];
                        row2[3] = row1[3];
                        //row2[4] = row1[4];
                        string LBBM = string.Empty;
                        LBBM = row2[0].ToString();
                        string LBMC = string.Empty;
                        LBMC = row2[1].ToString();
                        //if大于2
                        //输出编码错误
                        string DQBM = string.Empty;
                        string FUBM = string.Empty;
                        string SFMJ = string.Empty;
                        string LBBM1 = string.Empty;
                        string LBBM2 = string.Empty;
                        DQBM = row2[0].ToString();
                        string query2 = "类别编码='" + row2[0] + "'";
                        if (table_DQFLSC.Select(query2).Count() > 0)
                        {
                            result = false;
                            row2[4] =  "类别编码重复";
                        }
                        if (i == 2)
                        {
                            FUBM = DQBM.Substring(0, 2);
                            if (row1[0].ToString().Length != 4)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }
                         
                        }
                        if (i == 3)
                        {
                            FUBM = DQBM.Substring(0, 4);
                            if (row1[0].ToString().Length != 7)
                            {
                                row2[4] = row2[4] + ";" + "类别编码长度错误";
                                result = false;
                            }
                           
                        }

                        else
                        { }
                        if (table_DQFLSC.Select("类别编码 = '" + LBBM + "'").Count() > 1)
                        {
                            row2[4] = row2[4] + ";" + "类别编码重复";
                            result = false;
                        }
                        //
                        //table_CHFLSC.Rows.Add(row2);

                        //    else
                        //    if编码有重复的
                        //        输出编码重复

                        //if (table_CHFLSC.Select(LBMC).Count() > 1)
                        //{
                        //    row2[4] = "类别名称重复";
                        //    result = false;
                        //}
                        LBBM1 = "类别编码='" + FUBM + "'";
                        DataRow[] FYJBM = table_DQFLSR.Select(LBBM1);
                        if (FYJBM.Count() == 0)
                        {
                            row2[4] = row2[4] + ";" + "未找到上级编码";
                            result = false;
                        }
                        else
                        {
                            if (Convert.ToInt16(FYJBM[0][3]) == 1)
                            {
                                row2[4] = row2[4] + ";" + "上级编码为末级";
                                result = false;
                            }
                            LBBM2 = "类别编码 like '" + FUBM + "%' and 级次 = '" + (Convert.ToInt16(FYJBM[0][2]) + 1) + "' and 类别名称='" + LBMC + "'";
                            DataRow[] LBMC1 = table_DQFLSC.Select(LBBM2);
                            if (LBMC1.Count() > 0)
                            {
                                row2[4] = row2[4] + ";" + "类别名称重复";
                                result = false;
                            }
                        }



                        table_DQFLSC.Rows.Add(row2);
                    }
                }

            }
            MiniExcel.SaveAs("C:\\输出\\地区分类校验结果.xlsx", table_DQFLSC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("地区分类校验成功");
            }
            else
            {
                MessageBox.Show("地区分类校验失败");
                return;
            }
            #region 供应商档案
            
            DataTable table_GYSDASR = MiniExcel.QueryAsDataTable("C:\\输入\\供应商档案.xlsx", useHeaderRow: true);
            DataTable table_GYSDASC = table_GYSDASR.Clone();

            DataColumn col16 = new DataColumn();
            col16.ColumnName = "供应商档案校验结果";
            table_GYSDASC.Columns.Add(col16);
            
           foreach (DataRow row in table_GYSDASR.Rows)
            {
              
               
                string GYSBM = row[0].ToString();
                string GYSMC = row[1].ToString();
                string GYSJC = row[2].ToString();
                string GYSLBBM = row[3].ToString();
                string GYSDQM = row[4].ToString();
                DataRow row2 = table_GYSDASC.NewRow();
                for (int i = 0; i < table_GYSDASR.Columns.Count; i++)
                {
                    row2[i] = row[i];
                }                        
                string query2 = "供应商编码='" + row2[0] + "'";
                if (table_GYSDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "供应商编码重复";
                }     
                query2 = "供应商名称='" + row2[1] + "'";
                if (table_GYSDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "组名称重复";
                }
                query2 = "供应商简称='" + row2[2] + "'";
                if (table_GYSDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "组名称重复";
                }
              
                if (table_GYSFLSC.Select("类别编码='"+ GYSLBBM + "'").Count() == 0)               
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "类别编码不存在于客户分类";
                }

                query2 = "地区码='" + row2[4] + "'";
                if (table_DQFLSC.Select("类别编码='" + GYSDQM + "'").Count() == 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "地区码不存在于地区表";
                }

                table_GYSDASC.Rows.Add(row2);
            }
            MiniExcel.SaveAs("C:\\输出\\供应商档案校验结果.xlsx", table_GYSDASC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("供应商档案校验成功");
            }
            else
            {
                MessageBox.Show("供应商档案校验失败");
                return;
            }
            #region 客户档案

            DataTable table_KHDASR = MiniExcel.QueryAsDataTable("C:\\输入\\客户档案.xlsx", useHeaderRow: true);
            DataTable table_KHDASC = table_KHDASR.Clone();

            DataColumn col17 = new DataColumn();
            col17.ColumnName = "客户商档案校验结果";
            table_KHDASC.Columns.Add(col17);

            foreach (DataRow row in table_KHDASR.Rows)
            {
                string KHBM = row[0].ToString();
                string KHMC = row[1].ToString();
                string KHJC = row[2].ToString();
                string KHLBBM = row[3].ToString();
                string KHDQM = row[4].ToString();
                DataRow row2 = table_KHDASC.NewRow();
                for (int i = 0; i < table_KHDASR.Columns.Count; i++)
                {
                    row2[i] = row[i];
                }
                string query2 = "供应商编码='" + row2[0] + "'";
                if (table_KHDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = "供应商商编码重复";
                }
                query2 = "供应商名称='" + row2[1] + "'";
                if (table_KHDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "供应商名称重复";
                }
                query2 = "供应商简称='" + row2[2] + "'";
                if (table_KHDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "供应商简名称重复";
                }
                query2 = "类别编码='" + row2[3] + "'";
                if (table_KHFLSC.Select("类别编码='" + KHLBBM + "'").Count() == 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "类别编码不存在于客户分类";
                }

                query2 = "地区码='" + row2[4] + "'";
                if (table_DQFLSC.Select("类别编码='" + KHDQM + "'").Count() == 0)
                {
                    result = false;
                    row2[48] = row2[48] + ";" + "地区码不存在于地区表";
                }

                table_KHDASC.Rows.Add(row2);
            }
            MiniExcel.SaveAs("C:\\输出\\客户档案校验结果.xlsx", table_KHDASC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("客户商档案校验成功");
            }
            else
            {
                MessageBox.Show("客户商档案校验失败");
                return;
            }
            #region 存货档案
            DataTable table_CHDASR = MiniExcel.QueryAsDataTable("C:\\输入\\存货档案.xlsx", useHeaderRow: true);           
            DataTable table_CHDASC = table_CHDASR.Clone();

            DataColumn col18 = new DataColumn();
            col18.ColumnName = "存货商档案校验结果";
            table_CHDASC.Columns.Add(col18);

            foreach (DataRow row in table_CHDASR.Rows)
            {
                string DWZBM = row[19].ToString();
                string ZJL = row[20].ToString(); 
                String DWZLX = row[18].ToString();              
                String CHFLM = row[4].ToString();
                
               
                DataRow row2 = table_CHDASC.NewRow();
                for (int i = 0; i < table_CHDASR.Columns.Count; i++)
                {
                    row2[i] = row[i];
                }
                string query2 = "存货编码='" + row2[0] + "'";
                if (table_CHDASC.Select(query2).Count() > 0)
                {
                    result = false;
                    row2[71] = "存货编码重复";
                }

                query2 = "存货名称='" + row2[2] + "'";
                if (table_CHDASC.Select(query2).Count() > 0)
                {
                    result = false;
                   
                    row2[71] = row2[71] + ";" + "存货名称重复";
                }
                
                if (table_CHFLSC.Select("类别编码='" + CHFLM + "'").Count() == 0)
                {
                    result = false;
                    row2[71] = row2[71] + ";" + "分类码不存在于存货分类";
                }
                query2 = "组编码='" + row2[19] + "'";
                if (table_JLDWSC.Select(query2).Count() > 0)
                {
                    query2 = "单位编码 =  '" + ZJL  + "' and 组编码 = '" + DWZBM + "' ";
                    DataRow[] JLDWYZ = table_JLDWSC.Select(query2);
                    if (JLDWYZ.Count() == 0)
                    {
                        row2[71] = row2[71] + ";" + "未在计量单位中找到";
                        result = false;
                    }                 
                }
                query2 = "组编码='" + row2[19] + "'";
                if (table_JLDWSC.Select(query2).Count() == 0)
                {
                    result = false;
                    row2[71] = row2[71] + ";" + "组编码不存在于计量单位表";
                }
                query2 = "组编码='" + row2[19] + "'";
                if (table_JLDWZSC.Select(query2).Count() > 0)
                {
                    query2 = "换算类型 =  '" + DWZLX  + "' and 组编码 = '" + DWZBM + "' ";
                    DataRow[] JLDWYZ = table_JLDWZSC.Select(query2);
                    if (JLDWYZ.Count() == 0)
                    {
                        row2[71] = row2[71] + ";" + "未在计量单位组中找到";
                        result = false;
                    }
                }
                query2 = "组编码='" + row2[19] + "'";
                if (table_JLDWZSC.Select(query2).Count() == 0)
                {
                    result = false;
                    row2[71] = row2[71] + ";" + "组编码不存在于计量单位组表";
                }
                table_CHDASC.Rows.Add(row2);
            }
            MiniExcel.SaveAs("C:\\输出\\存货档案校验结果.xlsx", table_CHDASC);
            //table_CHFLSC.Rows.Add(row2);
            #endregion
            if (result)
            {
                MessageBox.Show("存货商档案校验成功");
            }
            else
            {
                MessageBox.Show("存货商档案校验失败");
                return;
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            List<string[]> test = new List<string[]>();
            for (int i = 0; i < 10; i++)
            {
                Random random = new Random(i);
                string[] tmp = new string[3];
                tmp[0] = "wxid";
                tmp[1] = "wxnaame" + i.ToString();
                tmp[2] = random.Next(10).ToString();
                test.Add(tmp);
            }
            //cols.Sort((x, y) => string.Compare(x[3], y[3], StringComparison.Ordinal));
            test.Sort((x, y) => string.Compare(y[2], x[2], StringComparison.Ordinal));
        }
    }
}
